Title: Year 12 / Computer Science / Theory / 8 - Databases / 8.3 - Data Definition Language (DDL) and Data Manipulation Language (DML)

Q: Write the required SQL code for creating a database
[[typebox:Back]]
A: CREATE DATABASE databasename;

Q: Write the required SQL code for creating a table
[[typebox:Back]]
A: CREATE TABLE <tablename>(
  <fieldname> <datatype> NOT NULL,
  PRIMARY KEY(<fieldname>)
);

Q: Write the required SQL code for foreign key
[[typebox:Back]]
A: FOREIGN KEY (field) REFERENCES tablename(field)

Q: Write the required SQL code for changing a table by adding a column
[[typebox:Back]]
A: ALTER TABLE <tablename>
ADD fieldname datatype;

Q: Write the required SQL code for changing a table by removing a column
[[typebox:Back]]
A: ALTER TABLE <tablename>
DROP COLUMN <fieldname> ;

Q: Write the required SQL code for altering a table by adding a primary key
[[typebox:Back]]
A: ALTER TABLE tablename
ADD PRIMARY KEY (fieldname);

Q: Explain the purpose of DDL
A: used for creation and modification of the database structure

Q: Explain the purpose of DML
A: used for queries and maintenance of data

Q: Write the required SQL code for creating a query
[[typebox:Back]]
A: SELECT fieldname
FROM tablename
WHERE searchcondition;

Q: Write the required SQL code for creating a query that is sorted by a field into ascending order
[[typebox:Back]]
A: SELECT ...
ORDER BY fieldname ASC;

Q: Write the required SQL code for creating a query that is sorts data by arranging identical data into groups
[[typebox:Back]]
A: SELECT ...
GROUP BY fieldname;

Q: Write the required SQL code for changing the data type of one field
[[typebox:Back]]
A: ALTER TABLE tablename
ALTER COLUMN fieldname datatype;

Q: Write the required SQL code for creating an inner join between 2 tables
[[typebox:Back]]
A: SELECT fieldnames
FROM table1, table2
ON table1.field1 = table2.field1;

Q: Write the required SQL code for creating an inner join between 3 tables
[[typebox:Back]]
A: SELECT fieldnames
FROM table1 INNER JOIN table 2
ON table1.field1 = table2.field1
INNER JOIN table3
ON table2.field1 = table3.field1;

Q: Write the required SQL code for adding data to a table
[[typebox:Back]]
A: INSERT INTO tablename(field1, field2, field3)
VALUES(value1, value2, value3);

Q: Write the required SQL code for deleting an entire table
[[typebox:Back]]
A: DELETE FROM tablename;

Q: Write the required SQL code for deleting data from a table
[[typebox:Back]]
A: DELETE FROM tablename
WHERE searchcondition;

Q: Write the required SQL code for updating data in a table
[[typebox:Back]]
A: UPDATE tablename
SET fieldname = value
WHERE condition;

Q: Write the required SQL code for querying a database to find all data that have a field that starts with "A"
[[typebox:Back]]
A: SELECT fieldname
FROM tablename
WHERE fieldname LIKE "A%";

Q: Write the required SQL code for querying a database to find all data that have an attribute that can be one of two values
[[typebox:Back]]
A: SELECT fieldname
FROM tablename
WHERE fieldname IN (value1, value2);

Q: Write the required SQL code for querying a database that prints out the amount of records
[[typebox:Back]]
A: SELECT COUNT(*) AS RecordAmount
FROM tablename;

Q: Write the required SQL code for querying a database that prints out the average of a field
[[typebox:Back]]
A: SELECT AVG(field) AS FieldAverage
FROM tablename;

Q: Write the required SQL code for querying a database that prints out the sum of a numerical field
[[typebox:Back]]
A: SELECT SUM(fieldname) AS FieldSum
FROM tablename;